﻿-- Initial database create script created.
-- Updates to this should be done through DB update scripts.
-- Created the database on 2011-05-08 by Kristín.
-- 2011-05-10 Updated schema and rerun
-- 2011-05-14 Modified Timelines table create script - table Memos dropped
-- 2011-05-16 Altered database creation KHB script

--CRETE TABLES--
CREATE TABLE [dbo].[About](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [content] [varchar](max) NOT NULL,
  [date_changed] [smalldatetime] NOT NULL,
  [changed_by] [varchar](max) NOT NULL,
PRIMARY KEY CLUSTERED
(
  [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Advertisement](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [title] [varchar](100) NULL,
  [posted] [smalldatetime] NOT NULL,
  [txt] [varchar](max) NULL,
  [url] [varchar](max) NULL,
  [isdisplayed] [bit] NULL,
PRIMARY KEY CLUSTERED
(
  [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Answers](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [polls_ID] [int] NOT NULL,
  [answer] [varchar](50) NOT NULL,
  [cnt] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
  [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[aspnet_Applications](
  [ApplicationName] [nvarchar](256) NOT NULL,
  [LoweredApplicationName] [nvarchar](256) NOT NULL,
  [ApplicationId] [uniqueidentifier] NOT NULL,
  [Description] [nvarchar](256) NULL,
PRIMARY KEY NONCLUSTERED
(
  [ApplicationId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
  [LoweredApplicationName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
  [ApplicationName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[aspnet_Membership](
  [ApplicationId] [uniqueidentifier] NOT NULL,
  [UserId] [uniqueidentifier] NOT NULL,
  [Password] [nvarchar](128) NOT NULL,
  [PasswordFormat] [int] NOT NULL,
  [PasswordSalt] [nvarchar](128) NOT NULL,
  [MobilePIN] [nvarchar](16) NULL,
  [Email] [nvarchar](256) NULL,
  [LoweredEmail] [nvarchar](256) NULL,
  [PasswordQuestion] [nvarchar](256) NULL,
  [PasswordAnswer] [nvarchar](128) NULL,
  [IsApproved] [bit] NOT NULL,
  [IsLockedOut] [bit] NOT NULL,
  [CreateDate] [datetime] NOT NULL,
  [LastLoginDate] [datetime] NOT NULL,
  [LastPasswordChangedDate] [datetime] NOT NULL,
  [LastLockoutDate] [datetime] NOT NULL,
  [FailedPasswordAttemptCount] [int] NOT NULL,
  [FailedPasswordAttemptWindowStart] [datetime] NOT NULL,
  [FailedPasswordAnswerAttemptCount] [int] NOT NULL,
  [FailedPasswordAnswerAttemptWindowStart] [datetime] NOT NULL,
  [Comment] [ntext] NULL,
  [phone_number] [varchar](20) NULL,
  [favorites] [varchar](max) NULL,
  [isActive] [bit] NULL,
  [user_role] [int] NULL,
  [title] [varchar](100) NULL,
  [fullName] [varchar](max) NULL,
PRIMARY KEY NONCLUSTERED
(
  [UserId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


CREATE TABLE [dbo].[aspnet_Paths](
  [ApplicationId] [uniqueidentifier] NOT NULL,
  [PathId] [uniqueidentifier] NOT NULL,
  [Path] [nvarchar](256) NOT NULL,
  [LoweredPath] [nvarchar](256) NOT NULL,
PRIMARY KEY NONCLUSTERED
(
  [PathId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[aspnet_PersonalizationAllUsers](
  [PathId] [uniqueidentifier] NOT NULL,
  [PageSettings] [image] NOT NULL,
  [LastUpdatedDate] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
  [PathId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[aspnet_PersonalizationPerUser](
  [Id] [uniqueidentifier] NOT NULL,
  [PathId] [uniqueidentifier] NULL,
  [UserId] [uniqueidentifier] NULL,
  [PageSettings] [image] NOT NULL,
  [LastUpdatedDate] [datetime] NOT NULL,
PRIMARY KEY NONCLUSTERED
(
  [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[aspnet_Profile](
  [UserId] [uniqueidentifier] NOT NULL,
  [PropertyNames] [ntext] NOT NULL,
  [PropertyValuesString] [ntext] NOT NULL,
  [PropertyValuesBinary] [image] NOT NULL,
  [LastUpdatedDate] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
  [UserId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[aspnet_Roles](
  [ApplicationId] [uniqueidentifier] NOT NULL,
  [RoleId] [uniqueidentifier] NOT NULL,
  [RoleName] [nvarchar](256) NOT NULL,
  [LoweredRoleName] [nvarchar](256) NOT NULL,
  [Description] [nvarchar](256) NULL,
PRIMARY KEY NONCLUSTERED
(
  [RoleId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[aspnet_SchemaVersions](
  [Feature] [nvarchar](128) NOT NULL,
  [CompatibleSchemaVersion] [nvarchar](128) NOT NULL,
  [IsCurrentVersion] [bit] NOT NULL,
PRIMARY KEY CLUSTERED
(
  [Feature] ASC,
  [CompatibleSchemaVersion] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[aspnet_Users](
  [ApplicationId] [uniqueidentifier] NOT NULL,
  [UserId] [uniqueidentifier] NOT NULL,
  [UserName] [nvarchar](256) NOT NULL,
  [LoweredUserName] [nvarchar](256) NOT NULL,
  [MobileAlias] [nvarchar](16) NULL,
  [IsAnonymous] [bit] NOT NULL,
  [LastActivityDate] [datetime] NOT NULL,
PRIMARY KEY NONCLUSTERED
(
  [UserId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[aspnet_UsersInRoles](
  [UserId] [uniqueidentifier] NOT NULL,
  [RoleId] [uniqueidentifier] NOT NULL,
PRIMARY KEY CLUSTERED
(
  [UserId] ASC,
  [RoleId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[aspnet_WebEvent_Events](
  [EventId] [char](32) NOT NULL,
  [EventTimeUtc] [datetime] NOT NULL,
  [EventTime] [datetime] NOT NULL,
  [EventType] [nvarchar](256) NOT NULL,
  [EventSequence] [decimal](19, 0) NOT NULL,
  [EventOccurrence] [decimal](19, 0) NOT NULL,
  [EventCode] [int] NOT NULL,
  [EventDetailCode] [int] NOT NULL,
  [Message] [nvarchar](1024) NULL,
  [ApplicationPath] [nvarchar](256) NULL,
  [ApplicationVirtualPath] [nvarchar](256) NULL,
  [MachineName] [nvarchar](256) NOT NULL,
  [RequestUrl] [nvarchar](1024) NULL,
  [ExceptionType] [nvarchar](256) NULL,
  [Details] [ntext] NULL,
PRIMARY KEY CLUSTERED
(
  [EventId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


CREATE TABLE [dbo].[Cateries](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [catery_name] [varchar](50) NOT NULL,
  [created] [smalldatetime] NULL,
PRIMARY KEY CLUSTERED
(
  [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[ContactUs](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [email] [varchar](100) NULL,
  [subj] [varchar](300) NOT NULL,
  [txt] [varchar](max) NULL,
  [unread] [bit] NOT NULL,
  [posted] [smalldatetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
  [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[Entries](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [entry_type] [int] NOT NULL,
  [title] [varchar](50) NOT NULL,
  [content] [varchar](max) NOT NULL,
  [catery] [int] NOT NULL,
  [posted] [smalldatetime] NULL,
  [added_by] [uniqueidentifier] NOT NULL,
  [entry_status] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
  [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[EntryOrigin](
  [entryID] [int] NOT NULL,
  [originID] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
  [entryID] ASC,
  [originID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Favorites](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [userID] [uniqueidentifier] NULL,
  [entry_ID] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
  [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[Origins](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [origin_date] [smalldatetime] NULL,
  [name] [varchar](100) NULL,
  [origin_type] [varchar](100) NULL,
  [url] [varchar](max) NULL,
PRIMARY KEY CLUSTERED
(
  [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[Polls](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [title] [varchar](100) NOT NULL,
  [posted] [smalldatetime] NOT NULL,
  [question] [varchar](100) NOT NULL,
  [opens] [smalldatetime] NOT NULL,
  [closes] [smalldatetime] NOT NULL,
  [userID] [uniqueidentifier] NULL,
PRIMARY KEY CLUSTERED
(
  [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

SET ANSI_PADDING OFF

CREATE TABLE [dbo].[Questions](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [userID] [uniqueidentifier] NOT NULL,
  [posted] [smalldatetime] NOT NULL,
  [subj] [varchar](300) NOT NULL,
  [txt] [varchar](max) NULL,
  [answer] [varchar](max) NULL,
  [answered_by] [uniqueidentifier] NULL,
  [answered_date] [smalldatetime] NULL,
PRIMARY KEY CLUSTERED
(
  [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[Timelines](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [chartID] [int] NOT NULL,
  [userID] [uniqueidentifier] NOT NULL,
  [x] [int] NOT NULL,
  [y1] [int] NULL,
  [y2] [int] NULL,
  [created_date] [smalldatetime] NOT NULL,
  [created_by] [uniqueidentifier] NOT NULL,
  [changed_date] [smalldatetime] NULL,
  [changed_by] [uniqueidentifier] NULL,
PRIMARY KEY CLUSTERED
(
  [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

--ATERATIONS AND FOREGN KEYS --
ALTER TABLE [dbo].[Answers]  WITH CHECK ADD FOREIGN KEY([polls_ID])
REFERENCES [dbo].[Polls] ([ID])


ALTER TABLE [dbo].[Answers] ADD  DEFAULT ((0)) FOR [cnt]

ALTER TABLE [dbo].[aspnet_Applications] ADD  DEFAULT (newid()) FOR [ApplicationId]

ALTER TABLE [dbo].[aspnet_Membership]  WITH CHECK ADD FOREIGN KEY([ApplicationId])
REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])


ALTER TABLE [dbo].[aspnet_Membership]  WITH CHECK ADD FOREIGN KEY([UserId])
REFERENCES [dbo].[aspnet_Users] ([UserId])


ALTER TABLE [dbo].[aspnet_Membership] ADD  DEFAULT ((0)) FOR [PasswordFormat]


ALTER TABLE [dbo].[aspnet_Membership] ADD  DEFAULT ((1)) FOR [user_role]


ALTER TABLE [dbo].[aspnet_Paths]  WITH CHECK ADD FOREIGN KEY([ApplicationId])
REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])


ALTER TABLE [dbo].[aspnet_Paths] ADD  DEFAULT (newid()) FOR [PathId]

ALTER TABLE [dbo].[aspnet_PersonalizationAllUsers]  WITH CHECK ADD FOREIGN KEY([PathId])
REFERENCES [dbo].[aspnet_Paths] ([PathId])

ALTER TABLE [dbo].[aspnet_PersonalizationPerUser]  WITH CHECK ADD FOREIGN KEY([PathId])
REFERENCES [dbo].[aspnet_Paths] ([PathId])


ALTER TABLE [dbo].[aspnet_PersonalizationPerUser]  WITH CHECK ADD FOREIGN KEY([UserId])
REFERENCES [dbo].[aspnet_Users] ([UserId])


ALTER TABLE [dbo].[aspnet_PersonalizationPerUser] ADD  DEFAULT (newid()) FOR [Id]

ALTER TABLE [dbo].[aspnet_Profile]  WITH CHECK ADD FOREIGN KEY([UserId])
REFERENCES [dbo].[aspnet_Users] ([UserId])

ALTER TABLE [dbo].[aspnet_Roles]  WITH CHECK ADD FOREIGN KEY([ApplicationId])
REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])


ALTER TABLE [dbo].[aspnet_Roles] ADD  DEFAULT (newid()) FOR [RoleId]

ALTER TABLE [dbo].[aspnet_Users]  WITH CHECK ADD FOREIGN KEY([ApplicationId])
REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])


ALTER TABLE [dbo].[aspnet_Users] ADD  DEFAULT (newid()) FOR [UserId]


ALTER TABLE [dbo].[aspnet_Users] ADD  DEFAULT (NULL) FOR [MobileAlias]


ALTER TABLE [dbo].[aspnet_Users] ADD  DEFAULT ((0)) FOR [IsAnonymous]

ALTER TABLE [dbo].[aspnet_UsersInRoles]  WITH CHECK ADD FOREIGN KEY([RoleId])
REFERENCES [dbo].[aspnet_Roles] ([RoleId])


ALTER TABLE [dbo].[aspnet_UsersInRoles]  WITH CHECK ADD FOREIGN KEY([UserId])
REFERENCES [dbo].[aspnet_Users] ([UserId])


ALTER TABLE [dbo].[Entries]  WITH CHECK ADD FOREIGN KEY([added_by])
REFERENCES [dbo].[aspnet_Users] ([UserId])


ALTER TABLE [dbo].[Entries]  WITH CHECK ADD FOREIGN KEY([catery])
REFERENCES [dbo].[Cateries] ([ID])


ALTER TABLE [dbo].[Entries] ADD  DEFAULT ('7D0CBF70-D6BD-42C8-AB42-7914593A24D6') FOR [added_by]


ALTER TABLE [dbo].[Entries] ADD  DEFAULT ((1)) FOR [entry_status]

ALTER TABLE [dbo].[EntryOrigin]  WITH CHECK ADD FOREIGN KEY([entryID])
REFERENCES [dbo].[Entries] ([ID])


ALTER TABLE [dbo].[EntryOrigin]  WITH CHECK ADD FOREIGN KEY([originID])
REFERENCES [dbo].[Origins] ([ID])


ALTER TABLE [dbo].[Favorites]  WITH CHECK ADD FOREIGN KEY([entry_ID])
REFERENCES [dbo].[Entries] ([ID])


ALTER TABLE [dbo].[Favorites]  WITH CHECK ADD FOREIGN KEY([userID])
REFERENCES [dbo].[aspnet_Users] ([UserId])


ALTER TABLE [dbo].[Polls]  WITH CHECK ADD FOREIGN KEY([userID])
REFERENCES [dbo].[aspnet_Users] ([UserId])


ALTER TABLE [dbo].[Questions]  WITH CHECK ADD FOREIGN KEY([userID])
REFERENCES [dbo].[aspnet_Users] ([UserId])

ALTER TABLE [dbo].[Timelines]  WITH CHECK ADD FOREIGN KEY([changed_by])
REFERENCES [dbo].[aspnet_Users] ([UserId])


ALTER TABLE [dbo].[Timelines]  WITH CHECK ADD FOREIGN KEY([created_by])
REFERENCES [dbo].[aspnet_Users] ([UserId])



ALTER TABLE [dbo].[Timelines]  WITH CHECK ADD FOREIGN KEY([userID])
REFERENCES [dbo].[aspnet_Users] ([UserId])


--VIEWS-- 
create view [dbo].[UserHandling] as
select UserName, RoleName, CreateDate, isActive from aspnet_Membership ml
right join aspnet_Users ul on(ul.UserId = ml.UserId) 
left join aspnet_UsersInRoles url on (ul.UserId = url.UserId)
left join aspnet_Roles rl on (url.RoleId = rl.RoleId)
